﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using NoORM.Core.DbDialect;
using System.Text.RegularExpressions;
using NoORM.Core.Contract;
using System.Text;

namespace NoORM.Core.DbAcceess
{
    public class Dao
    {
        /// <summary>
        /// 数据库连接构建者
        /// </summary>
        private readonly ConnectionBuilder connectionBuilder;

        /// <summary>
        /// 数据库方言构建者
        /// </summary>
        private readonly DbDialectBuilder dialectBuilder;

        public Dao(ConnectionBuilder connectionBuilder, DbDialectBuilder dialectBuilder)
        {
            this.connectionBuilder = connectionBuilder;
            this.dialectBuilder = dialectBuilder;
        }

        /// <summary>
        /// 查询一行数据
        /// </summary>
        /// <param name="config">连接字符串配置</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>数据封装在键值对中</returns>
        public Task<Dictionary<string, object>> ExecuteDictionaryAsync(ConnectionStringConfig config, string sql, Dictionary<string, object> parameters)
        {
            return Task.Run(() =>
            {
                IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
                Dictionary<string, object> result = new Dictionary<string, object>();

                string pattern = "#([a-zA-Z0-9_]+)#";
                sql = Regex.Replace(sql, pattern, dialect.BuildParameterName("$1"));

                using (IDbConnection conn = connectionBuilder.Builder(config))
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    foreach (string key in parameters.Keys)
                    {
                        IDbDataParameter p = cmd.CreateParameter();
                        p.ParameterName = dialect.CreateParameterName(key);
                        p.Value = parameters[key];
                        cmd.Parameters.Add(p);
                    }
                    conn.Open();
                    using (IDataReader dr = cmd.ExecuteReader())
                    {
                        result = dr.ToJson();
                    }
                }
                return result;
            });
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="config">连接字符串配置</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>返回的数据封装在键值对集合中</returns>
        public Task<List<Dictionary<string, object>>> ExecuteListAsync(ConnectionStringConfig config, string sql, Dictionary<string, object> parameters)
        {

            return Task.Run(() =>
            {
                if (parameters == null)
                {
                    parameters = new Dictionary<string, object>();
                }
                IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);

                string pattern = "#([a-zA-Z0-9_]+)#";
                sql = Regex.Replace(sql, pattern, dialect.BuildParameterName("$1"));

                List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();

                using (IDbConnection conn = this.connectionBuilder.Builder(config))
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    foreach (string key in parameters.Keys)
                    {
                        IDbDataParameter p = cmd.CreateParameter();
                        p.ParameterName = dialect.CreateParameterName(key);
                        p.Value = parameters[key];
                        cmd.Parameters.Add(p);
                    }
                    conn.Open();
                    using (IDataReader dr = cmd.ExecuteReader())
                    {
                        result = dr.ToJsonList();
                    }
                }
                return result;
            });
        }

        /// <summary>
        /// 执行非查询操作
        /// </summary>
        /// <param name="config">连接字符串配置</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>受影响的数据行数</returns>
        public Task<int> ExecuteNoQueryAsync(ConnectionStringConfig config, string sql, Dictionary<string, object> parameters)
        {
            return Task.Run(() =>
            {
                int result = 0;
                IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
                string pattern = "#([a-zA-Z0-9_]+)#";
                sql = Regex.Replace(sql, pattern, dialect.BuildParameterName("$1"));

                using (IDbConnection conn = this.connectionBuilder.Builder(config))
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    foreach (string key in parameters.Keys)
                    {
                        IDbDataParameter p = cmd.CreateParameter();
                        p.ParameterName = dialect.CreateParameterName(key);
                        p.Value = parameters[key];
                        cmd.Parameters.Add(p);
                    }
                    conn.Open();
                    result = cmd.ExecuteNonQuery();
                }
                return result;
            });
        }

        /// <summary>
        /// 返回一行一列数据
        /// </summary>
        /// <param name="config">连接字符串配置</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public Task<object> ExecuteScalarAsync(ConnectionStringConfig config, string sql, Dictionary<string, object> parameters)
        {
            return Task.Run(() =>
            {
                IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
                object result = null;

                string pattern = "#([a-zA-Z0-9_]+)#";
                sql = Regex.Replace(sql, pattern, dialect.BuildParameterName("$1"));

                using (IDbConnection conn = connectionBuilder.Builder(config))
                {
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    foreach (string key in parameters.Keys)
                    {
                        IDbDataParameter p = cmd.CreateParameter();
                        p.ParameterName = dialect.CreateParameterName(key);
                        p.Value = parameters[key];
                        cmd.Parameters.Add(p);
                    }
                    conn.Open();
                    result = cmd.ExecuteScalar();
                }
                return result;
            });
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="config">连接字符串配置</param>
        /// <param name="transItems">sql语句及其参数集合</param>
        /// <returns>事务执行成功与否</returns>
        public Task<bool> ExecuteTransaction(ConnectionStringConfig config, List<Tuple<string, Dictionary<string, object>>> transItems)
        {
            return Task.Run(() =>
            {
                bool result = false;
                IDbDialect dialect = this.dialectBuilder.Builder(config.ConnectionClassName);
                string pattern = "#([a-zA-Z0-9_]+)#";
                using (IDbConnection conn = connectionBuilder.Builder(config))
                {
                    conn.Open();
                    using (IDbTransaction trans = conn.BeginTransaction())
                    {
                        try
                        {
                            foreach (var tuple in transItems)
                            {
                                string sql = tuple.Item1;
                                Dictionary<string, object> parameters = tuple.Item2;
                                sql = Regex.Replace(sql, pattern, dialect.BuildParameterName("$1"));
                                IDbCommand cmd = conn.CreateCommand();
                                cmd.Transaction = trans;
                                cmd.CommandText = sql;
                                foreach (string key in parameters.Keys)
                                {
                                    IDbDataParameter p = cmd.CreateParameter();
                                    p.ParameterName = dialect.CreateParameterName(key);
                                    p.Value = parameters[key];
                                    cmd.Parameters.Add(p);
                                }
                                cmd.ExecuteNonQuery();
                            }
                            trans.Commit();
                            result = true;
                        }
                        catch (Exception ex)
                        {
                            result = false;
                            throw ex;
                        }
                    }
                }
                return result;
            });
        }
    }
}
